package com.kfm.jdbc.day0324;

import java.sql.*;
import java.util.Scanner;

public class Demo02 {

    public static void main(String[] args) {
        transfer3();
    }

    public static void login() {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String name = sc.next();
        System.out.println("请输入密码：");
        String password = sc.next();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=root&password=");
            String sql = "select * from users where name = ? and password = ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setObject(1, name);
            statement.setObject(2, password);
            ResultSet set = statement.executeQuery();
            if (set.next()) {
                System.out.println("登录成功");
            } else {
                System.out.println("登录失败");
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public static void procCall() {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入你要计算谁的年薪：");
        Integer id = sc.nextInt();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=root&password=");
            CallableStatement callableStatement = connection.prepareCall("{call `nx`(?)}");
            callableStatement.setObject(1, id);
            ResultSet resultSet = callableStatement.executeQuery();
            if (resultSet.next()) {
                System.out.println(resultSet.getObject(1));
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 事务

    /**
     * 不在事务中进行转账操作
     */
    public static void transfer() {
        try {
            //Class.forName("com.mysql.cj.jdbc.Driver");
            //Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=root&password=");
            //// 小王转出8000
            //String sql = "update account set balance = balance - 8000 where name = '小王'";
            //PreparedStatement statement = connection.prepareStatement(sql);
            //int i = statement.executeUpdate();
            //if (i > 0) {
            //    System.out.println("小王转出成功");
            //} else {
            //    System.out.println("小王转出失败");
            //}
            DBUtils dbUtils = new DBUtils();
            String sql = "update account set balance = balance - 8000 where name = '小王'";
            int i = dbUtils.executeUpdate(sql);
            if (i > 0) {
                System.out.println("小王转出成功");
            } else {
                System.out.println("小王转出失败");
            }
            // 小王搜转入8000
            sql = "update account set balance = balance + 8000 where name = '小王嫂'";
            int i1 = dbUtils.executeUpdate(sql);
            if (i1 > 0) {
                System.out.println("小王嫂收到转账");
            } else {
                System.out.println("小王嫂没收到转账");
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void transfer2() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=root&password=");
            // 小王转出8000
            String sql = "update account set balance = balance - 8000 where name = '小王'";
            PreparedStatement statement = connection.prepareStatement(sql);
            int i = statement.executeUpdate();
            if (i > 0) {
                System.out.println("小王转出成功");
            } else {
                System.out.println("小王转出失败");
            }
            // 程序出现异常
            int num = 1 / 0;
            // 小王搜转入8000
            sql = "update account set balance = balance + 8000 where name = '小王嫂'";
            statement = connection.prepareStatement(sql);
            int i1 = statement.executeUpdate();
            if (i1 > 0) {
                System.out.println("小王嫂收到转账");
            } else {
                System.out.println("小王嫂没收到转账");
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 在事务中进行转账操作
     *
     */
    public static void transfer3() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=root&password=");
            // 将事务设置为手动提交 false --> 手动提交 true --> 自动提交 （默认）
            connection.setAutoCommit(false);
            // 小王转出8000
            String sql = "update account set balance = balance - 8000 where name = '小王'";
            PreparedStatement statement = connection.prepareStatement(sql);
            // 执行事务
            int i = statement.executeUpdate();
            if (i > 0) {
                System.out.println("小王转出成功");
            } else {
                System.out.println("小王转出失败");
            }
            // 程序出现异常
            //int num = 1 / 0;
            // 小王搜转入8000
            sql = "update account set balance = balance + 8000 where name = '小王嫂'";
            statement = connection.prepareStatement(sql);
            // 执行事务
            int i1 = statement.executeUpdate();
            if (i1 > 0) {
                System.out.println("小王嫂收到转账");
            } else {
                System.out.println("小王嫂没收到转账");
            }
            // 提交事务
            connection.commit();
        } catch (Exception e) {
            try {
                connection.rollback();
                System.out.println("发生异常，转账原路返回");
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }
}
